Hello and welcome.
In this video you'll learn how to write
sub-queries or nested selects statements.
Sub-queries or sub selects are like regular queries
but placed within parentheses
and nested inside another query.
This allows you to form
more powerful queries than
would have been otherwise possible.
An example of a nested query is shown.
In this example, the sub-query is
inside the where clause of another query.
Consider the employees table from the previous video.
The first few rows of data are shown here.
The table contains several columns,
including an employee ID,
first name, last name, salary, etc.
We will now go over some examples involving this table.
Let's consider a scenario which may
necessitate the use of sub-queries.
Let's say, we want to retrieve the list of employees
who earn more than the average salary.
To do so, you could try this code.
Select * from employees,
where salary > AVG(salary).
However, running this query will
result in an error like the one shown.
Indicating an invalid use of the aggregate function.
One of the limitations of built in
aggregate functions, like the average function,
is that they cannot always be
evaluated in the WHERE clause.
So to evaluate a function
like average in the WHERE clause,
we can make use of
a sub-select expression like the one shown here.
Select EMP_ID, F_NAME, L_NAME,
SALARY from employees where SALARY
< ( select AVG (SALARY) from employees).
Notice that the average function is
evaluated in the first part of the sub-query.
Allowing us to circumvent the limitation of
evaluating it directly in the WHERE clause.
The sub-select doesn't just
have to go in the WHERE clause.
It can also go in other parts of
the query, such as in the list of columns to be selected.
Such sub-queries are called column expressions.
Now, let's look at a scenario where
we might want to use a column expression.
Say we wanted to compare the salary of
each employee with the average salary.
We could try a query like select EMP_ID,
SALARY, AVG(SALARY) AS AVG_SALARY from employees.
Running this query will result in an error
indicating that no group by clause is specified.
We can circumvent this error by using
the average function in
a sub-query placed in the list of the columns.
For example, select EMP_ID, SALARY,
(select AVG(SALARY) from employees
) AS AVG_SALARY from employees.
Another option is to make
the sub-query be part of the FROM clause.
Sub-queries like these are sometimes
called derived tables or table expressions.
Because the outer query uses the results
of the sub-query as a data source.
Let's look at an example to create
a table expression that
contains nonsensitive employee information.
Select * from (select EMP_ID,
F_NAME, L_NAME, DEP_ID from employees) AS EMP4ALL.
The derived table in a sub-query does not
include sensitive fields like date of birth or salary.
This example is a trivial one, and
we could just as easily have
included the columns in the outer query.
However, such derived tables can prove to be powerful in
more complex situations such as when
working with multiple tables and doing joins.
In this video, you have seen how
sub-queries and nested queries can be
used to form richer queries and how they can
overcome some of the limitations of aggregate functions.
You also learned to use sub-queries in the WHERE clause,
in the list of columns and in the FROM clause.
Thanks for watching this video